package es.unileon.is.finalproyect.users;

import java.sql.*;
import java.util.*;

import es.unileon.is.finalproyect.dbconnection.ApplicationConnection;
import es.unileon.is.finalproyect.exceptions.UniqueException;
import es.unileon.is.finalproyect.texts.*;

public class Group {
	private int _groupID;
	private String _groupName;
	
	public boolean assignText(User admin, Text t){
		if(!admin.isAdministrator()){
			throw new RuntimeException("You do not have enough privileges to perform this operation");
		}
		int textNumber = t.getID();
		try {
			PreparedStatement ps = ApplicationConnection.getConnection().prepareStatement("SELECT textNumber FROM ASSIGNED_TEXTS WHERE groupID = ? AND textNumber = ?");
			ps.setInt(1,_groupID);
			ps.setInt(2, textNumber);
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				return false;
			}else{
				ps = ApplicationConnection.getConnection().prepareStatement("INSERT INTO ASSIGNED_TEXTS (groupID, textNumber) VALUES (?,?)");
				ps.setInt(1,_groupID);
				ps.setInt(2, textNumber);
				ps.execute();
				List<User> list = getAssignedUsers();
				for(User u:list){
					u.addPendingText(admin, t);
				}
				return true;
			}
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage());
		}
	}
	
	public boolean assignUser(User admin, User u){
		if(!admin.isAdministrator()){
			throw new RuntimeException("You do not have enough privileges to perform this operation");
		}
		try {
			ApplicationConnection.getConnection().prepareStatement("INSERT INTO MEMBERS (userID,groupID) VALUES ("+u.getID()+","+_groupID+")").execute();
			List<Text> list = getAssignedTexts();
			for(Text t:list){
				u.addPendingText(admin, t);
			}
			return true;
		} catch (SQLException e) {
			return false;
			//lanzar excepcion
		}
	}
	
	public List<Text> getAssignedTexts(){
		ResultSet rs;
		List<Text> list = new ArrayList<Text>();
		try {
			rs = ApplicationConnection.getConnection().prepareStatement("SELECT textNumber FROM ASSIGNED_TEXTS WHERE groupID = " + _groupID).executeQuery();
			while(rs.next()){
				list.add(Text.requestText(rs.getInt(1)));
			} 
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage());
		}
			return list;
		
	}
	public List<Integer> getAssignedTextsID(){
		ResultSet rs;
		List<Integer> list = new ArrayList<Integer>();
		try {
			rs = ApplicationConnection.getConnection().prepareStatement("SELECT textNumber FROM ASSIGNED_TEXTS WHERE groupID = " + _groupID).executeQuery();
			while(rs.next()){
				list.add(rs.getInt(1));
			} 
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage());
		}
			return list;
		
	}
	
	public List<User> getAssignedUsers() {
		ResultSet rs;
		List<User> list = new ArrayList<User>();
		try{
			rs= ApplicationConnection.getConnection().prepareStatement("SELECT userID FROM MEMBERS WHERE groupID = "+ _groupID).executeQuery();
			while(rs.next()){
				list.add(User.requestUser(rs.getInt(1)));
			}
		}catch (SQLException e) {
			throw new RuntimeException(e.getMessage());
		}
		return list;
	}
	
	public List<Integer> getAssignedUsersID() {
		ResultSet rs;
		List<Integer> list = new ArrayList<Integer>();
		try{
			rs = ApplicationConnection.getConnection().prepareStatement("SELECT userID FROM MEMBERS WHERE groupID ="+ _groupID).executeQuery();
			while(rs.next()){
				list.add(rs.getInt(1));
			}
		}catch (SQLException e) {
			// lanzar excepcion
		}
		return list;
	}
	
	public int getID(){
		return _groupID;
	}
	
	public String getName(){
		return _groupName;
	}
	
	public boolean setName(User admin, String name){
		if(!admin.isAdministrator()){
			throw new RuntimeException("You do not have enough privileges to perform this operation");
		}
		_groupName=name;
		try {
			ApplicationConnection.getConnection().prepareStatement("UPDATE GROUPS SET name = '"+_groupName+"' WHERE groupId = "+_groupID).execute();
			return true;
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage());
		}
	}

	
	public Group (User admin, String name) throws UniqueException{
		if(!admin.isAdministrator()){
			throw new RuntimeException("You do not have enough privileges to perform this operation");
		}
		_groupName = name;
		try {
			ApplicationConnection.getConnection().prepareStatement("INSERT INTO GROUPS (name) VALUES ('"+_groupName+"')").execute();
			ResultSet rs= ApplicationConnection.getConnection().prepareStatement("SELECT groupID FROM GROUPS WHERE name = '"+_groupName+"'").executeQuery();
			if(rs.next()){
				_groupID=rs.getInt(1);
			} else{//lanza excepcion
			}
		} catch (SQLException e) {
			String msg = e.getMessage();
			if(msg.contains("UK_GROUPS")){
				throw new UniqueException("That is the name of other group. Please, use other name");
			}
		}
		
	}
	
	//Metodo que devuelve todos los grupos de la BD.
	public static List<Group> requestAllGroups(User admin){
		if(!admin.isAdministrator()){
			throw new RuntimeException("You do not have enough privileges to perform this operation");
		}
		List<Group> list = new ArrayList<Group>();
		try {
			ResultSet rs = ApplicationConnection.getConnection().prepareStatement("SELECT groupID FROM GROUPS").executeQuery();
			while(rs.next()){
				list.add(requestGroup(rs.getInt(1)));
			}
		} catch (SQLException e) {
			throw new RuntimeException("No hay grupos en la base de datos");
		}
		return list;
	}
	
	
	public static Group requestGroup(int groupID){
		return new Group(groupID);
	}
	
	private Group(int groupID){
		_groupID=groupID;
		try {
			ResultSet rs=ApplicationConnection.getConnection().prepareStatement("SELECT name FROM GROUPS WHERE groupID = "+_groupID).executeQuery();
			if(rs.next()){
				_groupName=rs.getString(1);
			}
			else{
				//lanza excepcion
			}
		} catch (SQLException e) {
			//lanzar excepcion
		}	
	}
	
	public boolean hasAssigned(Text t){
		try{
			PreparedStatement ps = ApplicationConnection.getConnection().prepareStatement("SELECT * FROM ASSIGNED_TEXTS WHERE groupID = ? AND textNumber = ?");
			ps.setInt(1, _groupID);
			ps.setInt(2, t.getID());
			ResultSet rs = ps.executeQuery();
			return rs.next();
		} catch (SQLException e){
			throw new RuntimeException(e.getMessage());
		}
	}
	
	public boolean hasAssigned(User u){
		try{
			PreparedStatement ps = ApplicationConnection.getConnection().prepareStatement("SELECT * FROM MEMBERS WHERE groupID = ? AND userID = ?");
			ps.setInt(1, _groupID);
			ps.setInt(2, u.getID());
			ResultSet rs = ps.executeQuery();
			return rs.next();
		} catch (SQLException e){
			throw new RuntimeException(e.getMessage());
		}
	}
	
	public void deleteGroup(User admin){
		if(!admin.isAdministrator()){
			throw new RuntimeException("You don't have enough privileges to perform this operation");
		}
		else{
			try {
				ApplicationConnection.getConnection().prepareStatement("DELETE FROM GROUPS WHERE groupID = "+_groupID).execute();
			} catch (SQLException e) {
				throw new RuntimeException("The group selected is not in the system");
			}
		}
	}
	

	
}
